package cn.swu.lzr.JDBC;

import cn.hutool.core.text.csv.CsvUtil;
import cn.swu.lzr.event.Event;
import com.mysql.cj.Session;
import jakarta.servlet.http.HttpSession;
import org.apache.commons.dbcp2.BasicDataSource;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class DBTools {
    private static  final String INSERT_EVENT = "INSERT INTO event (date,event,picture) VALUES(?,?,?)";
    private static final String DELETE_BY_ID = "DELETE FROM event WHERE id = ?";

    private static final  String FIND_ALL = "SELECT * FROM EVENT";

    private static final  String FIND_EVENT_BY_ID = "SELECT * FROM EVENT WHERE ID = ?";

    private static final  String UPDATE_EVENT ="UPDATE event SET date = ?, event = ?,picture = ? WHERE id = ?";

    private static final String getCount ="SELECT COUNT(*) FROM event";

    private static final String FIND_A_TO_B= "select * from event limit ?,5;";
    public boolean insertEvent(Event event) throws SQLException
    {

       try {
           Connection connection = DBUtil.getConnection();


           PreparedStatement preparedStatement = connection.prepareStatement(INSERT_EVENT);

           preparedStatement.setString(1, event.getDate());
           preparedStatement.setString(2,  event.getEvent());
           preparedStatement.setString(3,event.getPicture());



           preparedStatement.executeUpdate();

           DBUtil.ClosePrepareStatement(preparedStatement);
           DBUtil.closeConnection(connection);
            return true;
       }catch (Exception e)
       {
           e.printStackTrace();;
       }
        return false;
    }

    public boolean DeleteById(Integer id) throws Exception
    {
        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(DELETE_BY_ID);

            preparedStatement.setInt(1,id);
            preparedStatement.executeUpdate();
            DBUtil.ClosePrepareStatement(preparedStatement);
            DBUtil.closeConnection(connection);
            return true;

        }catch (Exception e)
        {
            e.printStackTrace();
        }
        return false;
    }

    public void UpdateEventById(Integer id,Event event)throws Exception
    {
        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(UPDATE_EVENT);

            preparedStatement.setString(1, event.getDate());
            preparedStatement.setString(2,  event.getEvent());
            preparedStatement.setString(3,event.getPicture());
            preparedStatement.setString(4,id.toString());
            preparedStatement.executeUpdate();

            DBUtil.ClosePrepareStatement(preparedStatement);
            DBUtil.closeConnection(connection);

        }catch (Exception e)
        {
            e.printStackTrace();;
        }

    }

    public List<Event> FindALL()throws Exception
    {
        List<Event> eventList =  new ArrayList<>();

        try {
            Connection connection = DBUtil.getConnection();
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery(FIND_ALL);
            while (resultSet.next())
            {
                Event event = ResultSetToEvent(resultSet);
                eventList.add(event);
            }
            DBUtil.CloseResultSet(resultSet);
            DBUtil.CloseStatement(statement);
            DBUtil.closeConnection(connection);

        }catch (Exception e)
        {
            e.printStackTrace();
        }
        return eventList;

    }


    public Event GetEventById(Integer id)throws Exception
    {
        Event event = new Event();
        Connection connection =  DBUtil.getConnection();

        PreparedStatement preparedStatement = connection.prepareStatement(FIND_EVENT_BY_ID);
        preparedStatement.setInt(1,id);

        ResultSet resultSet = preparedStatement.executeQuery();

        if (resultSet.next())
        {
             event = ResultSetToEvent(resultSet);
        }
        else
        {
             event = null;
        }
        DBUtil.CloseResultSet(resultSet);
        DBUtil.ClosePrepareStatement(preparedStatement);
        DBUtil.closeConnection(connection);
        return event;
    }


    private Event ResultSetToEvent(ResultSet resultSet)throws Exception
    {
       Event event = new Event();
       event.setId(resultSet.getInt("id"));
       event.setDate(resultSet.getString("date"));
       event.setEvent(resultSet.getString("event"));
       event.setPicture(resultSet.getString("picture"));
       return event;
    }


    public List<Event> SearchBy(String type,String key)throws Exception
    {
        List<Event> eventList =  new ArrayList<>();

        try {
            Connection connection = DBUtil.getConnection();

            Statement statement = connection.createStatement();
            key = "%"+key+"%";
            String template = "select * from event where %s like '%%%s%%'";
            String sql = String.format(template, type, key);

            ResultSet resultSet = statement.executeQuery(sql);
            while (resultSet.next())
            {
                Event event = ResultSetToEvent(resultSet);
                eventList.add(event);
            }
            DBUtil.CloseResultSet(resultSet);
            DBUtil.CloseStatement(statement);
            DBUtil.closeConnection(connection);

        }catch (Exception e)
        {
            e.printStackTrace();
        }
        return eventList;

    }

    public int getCount() throws SQLException {

        int rows = 0;

        Connection connection = DBUtil.getConnection();
        Statement statement = connection.createStatement();
        ResultSet resultSet =  statement.executeQuery(getCount);
        if (resultSet.next())
        {
            rows = resultSet.getInt(1);
        }

        return  rows;

    }

    public List<Event> Find_Page( int begin)throws Exception
    {
        List<Event> eventList =  new ArrayList<>();


        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(FIND_A_TO_B);
            preparedStatement.setInt(1,begin);
            ResultSet resultSet = preparedStatement.executeQuery();

            while (resultSet.next())
            {
                Event event = ResultSetToEvent(resultSet);
                eventList.add(event);
            }
            DBUtil.CloseResultSet(resultSet);
            DBUtil.ClosePrepareStatement(preparedStatement);
            DBUtil.closeConnection(connection);

        }catch (Exception e)
        {
            e.printStackTrace();
        }
        return eventList;

    }

}


